from rest_framework.response import Response
from rest_framework import status
from rest_framework.viewsets import ModelViewSet
from rest_framework import filters
from django_filters.rest_framework import DjangoFilterBackend
from django.db.models import Q, F, Func
from django.conf import settings
import pymysql, xlrd
from django.http import StreamingHttpResponse
from django.utils.encoding import escape_uri_path

from equity_mall.utils.pages import MyPage
from managements.filters import *
from managements.serializers1 import *
from equity_mall.utils.utils import get_level_list
from equity_mall.utils.qiniu_storage import pic_upload
from equity_admin.tasks import operation_merchant_bank_quest


class AliDiscountCouponModelViewSet(ModelViewSet):

    pagination_class = MyPage
    search_fields = ('activity_name', 'template_id')
    filter_class = AliDiscountCouponFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        # queryset = AliDiscountCoupon.objects.filter(distribution_platform_id=1)
        if level.level == 0:
            queryset = AliDiscountCoupon.objects.all().order_by('-id')
        elif level.level == 1:
            queryset = AliDiscountCoupon.objects.filter(level__prov_id=level.id).order_by('-id')
        elif level.level == 2:
            queryset = AliDiscountCoupon.objects.filter(level__city_id=level.id).order_by('-id')
        elif level.level == 3:
            queryset = AliDiscountCoupon.objects.filter(level__district_id=level.id).order_by('-id')
        else:
            queryset = AliDiscountCoupon.objects.filter(level__branch_id=level.id).order_by('-id')
        return queryset.exclude(distribution_platform_id=6).exclude(distribution_platform_id=5).order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return AliDiscountCouponListModelSerializer
        else:
            return AliDiscountCouponRetrieveModelSerializer


class AliDiscountCouponLevelModelViewSet(ModelViewSet):

    pagination_class = MyPage
    search_fields = ('level__name', )
    filter_class = AliDiscountCouponLevelFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        sort = self.request.query_params.get('sort', '0')
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = AliDiscountCouponLevel.objects.all()
        elif level.level == 1:
            queryset = AliDiscountCouponLevel.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = AliDiscountCouponLevel.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = AliDiscountCouponLevel.objects.filter(level__district_id=level.id)
        else:
            queryset = AliDiscountCouponLevel.objects.filter(level__branch_id=level.id)
        if sort == '0':
            queryset = queryset.order_by('max_number', 'id')
        elif sort == '1':
            queryset = queryset.order_by('-max_number', 'id')
        elif sort == '2':
            queryset = queryset.order_by('take_number', 'id')
        elif sort == '3':
            queryset = queryset.order_by('-take_number', 'id')
        elif sort == '4':
            queryset = queryset.order_by('remain_number', 'id')
        elif sort == '5':
            queryset = queryset.order_by('-remain_number', 'id')
        elif sort == '6':
            queryset = queryset.order_by(F('take_number') - F('receive_number'), 'id')
        elif sort == '7':
            queryset = queryset.order_by(-(F('take_number') - F('receive_number')), 'id')
        elif sort == '8':
            queryset = queryset.order_by(F('receive_number') - F('use_number'), 'id')
        elif sort == '9':
            queryset = queryset.order_by(-(F('receive_number') - F('use_number')), 'id')
        elif sort == '10':
            queryset = queryset.order_by('use_number', 'id')
        elif sort == '11':
            queryset = queryset.order_by('-use_number', 'id')
        else:
            queryset = queryset.order_by('level__level', 'id')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return AliDiscountCouponLevelListModelSerializer
        else:
            return AliDiscountCouponLevelRetrieveModelSerializer


class AliDiscountCouponInfoModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = AliDiscountCouponInfoFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('mobile', )

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        alidiscountcoupon_level_id = self.request.query_params.get('alidiscountcoupon_level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        try:
            if '_mutable' in self.request.data.__dict__:
                self.request.data.__dict__['_mutable'] = True
        except:
            pass
        if alidiscountcoupon_level_id:
            try:
                couponlevel = AliDiscountCouponLevel.objects.get(id=alidiscountcoupon_level_id)
                self.request.data['acitivty_info'] = AliDiscountCouponLevelListModelSerializer(couponlevel).data
            except:
                self.request.data['acitivty_info'] = {}
        if level.level == 0:
            queryset = AliDiscountCouponInfo.objects.all()
        elif level.level == 1:
            queryset = AliDiscountCouponInfo.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = AliDiscountCouponInfo.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = AliDiscountCouponInfo.objects.filter(level__district_id=level.id)
        else:
            queryset = AliDiscountCouponInfo.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return AliDiscountCouponInfoListModelSerializer
        else:
            return AliDiscountCouponInfoRetrieveModelSerializer


class CashActivityModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('name', )
    filter_class = CashActivityFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        customizeactivity_id = self.request.query_params.get('customizeactivity_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = CashActivity.objects.all()
        elif level.level == 1:
            queryset = CashActivity.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = CashActivity.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = CashActivity.objects.filter(level__district_id=level.id)
        else:
            queryset = CashActivity.objects.filter(level__branch_id=level.id)
        if customizeactivity_id:
            queryset = queryset.exclude(customizeactivityrules__customizeactivity_id=customizeactivity_id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return CashActivityListModelSerializer
        else:
            return CashActivityRetrieveModelSerializer


class CashActivityLevelModelViewSet(ModelViewSet):

    pagination_class = MyPage
    search_fields = ('level__name', )
    filter_class = CashActivityLevelFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        sort = self.request.query_params.get('sort', '')
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = CashActivityLevel.objects.filter()
        elif level.level == 1:
            queryset = CashActivityLevel.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = CashActivityLevel.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = CashActivityLevel.objects.filter(level__district_id=level.id)
        else:
            queryset = CashActivityLevel.objects.filter(level__branch_id=level.id)
        if sort == '0':
            queryset = queryset.order_by('p_max_amount', 'id')
        elif sort == '1':
            queryset = queryset.order_by('-p_max_amount', 'id')
        elif sort == '2':
            queryset = queryset.order_by('p_take_amount', 'id')
        elif sort == '3':
            queryset = queryset.order_by('-p_take_amount', 'id')
        elif sort == '4':
            queryset = queryset.order_by('p_remain_amount', 'id')
        elif sort == '5':
            queryset = queryset.order_by('-p_remain_amount', 'id')
        elif sort == '6':
            queryset = queryset.order_by(F('p_take_amount') - F('p_receive_amount'), 'id')
        elif sort == '7':
            queryset = queryset.order_by(-(F('p_take_amount') - F('p_receive_amount')), 'id')
        elif sort == '8':
            queryset = queryset.order_by('p_receive_amount', 'id')
        elif sort == '9':
            queryset = queryset.order_by('-p_receive_amount', 'id')
        else:
            queryset = queryset.order_by('level__level', 'id')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return CashActivityLevelListModelSerializer
        else:
            return CashActivityLevelRetrieveModelSerializer

    def export_cashactivitylevel(self, obj):
        activity_id = self.request.query_params.get('activity_id', None)
        search = self.request.query_params.get('search', None)
        dimensionality = self.request.query_params.get('dimensionality', '0,1,2,3,4')
        user = self.request.iser
        if not search:
            if user.level.level == 0:
                condition = f'WHERE level IN ({dimensionality})'
            elif user.level.level == 1:
                condition = f'WHERE prov_id={user.level_id} AND level IN ({dimensionality})'
            elif user.level.level == 2:
                condition = f'WHERE city_id={user.level_id} AND level IN ({dimensionality})'
            elif user.level.level == 3:
                condition = f'WHERE district_id={user.level_id} AND level IN ({dimensionality})'
            else:
                condition = f'WHERE branch_id={user.level_id} AND level IN ({dimensionality})'
        else:
            if user.level.level == 0:
                condition = f'WHERE name LIKE "%{search}%" AND level IN ({dimensionality})'
            elif user.level.level == 1:
                condition = f'WHERE prov_id={user.level_id} AND name LIKE "%{search}%" AND level IN ({dimensionality})'
            elif user.level.level == 2:
                condition = f'WHERE city_id={user.level_id} AND name LIKE "%{search}%" AND level IN ({dimensionality})'
            elif user.level.level == 3:
                condition = f'WHERE district_id={user.level_id} AND name LIKE "%{search}%" AND level IN ({dimensionality})'
            else:
                condition = f'WHERE branch_id={user.level_id} AND name LIKE "%{search}%" AND level IN ({dimensionality})'
        if not activity_id:
            condition1 = 'WHERE'
        else:
            condition1 = f'WHERE cash_activity_id="{activity_id}" AND'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        sql = f'''
            SELECT c.*,d.name,account,prov_name,city_name,district_name,branch_name,`level` FROM (SELECT a.*,b.activity_name FROM (SELECT id,level_id,cash_activity_id,p_max_amount,p_take_amount,p_remain_amount,(p_take_amount-p_receive_amount)AS p_unclaimed_amount,p_receive_amount,remain_amount,max_amount,receive_amount,take_amount,(take_amount-receive_amount)AS unclaimed_amount FROM tb_cash_activity_level {condition1} level_id IN (SELECT id FROM tb_u_level {condition}))AS a LEFT JOIN (SELECT id,name AS activity_name FROM tb_cash_activity)AS b ON a.cash_activity_id=b.id)AS c LEFT JOIN (SELECT id,name,account,prov_name,city_name,district_name,branch_name,`level` FROM tb_u_level)AS d ON c.level_id=d.id ORDER BY `level`;
            '''
        cursor.execute(sql)
        ziduan = ['id', '活动名称', '机构名称', '机构号', '省', '市', '区县', '网点', '活动预算', '已派发额度', '剩余预算', '待领取', '已领取', '机构分得预算', '机构已派发额度', '机构剩余可派发额度', '机构待领取额度', '机构已领取额度']
        cols = ['id', 'activity_name', 'name', 'account', 'prov_name', 'city_name', 'district_name', 'branch_name', 'p_max_amount', 'p_take_amount', 'p_remain_amount', 'p_unclaimed_amount', 'p_receive_amount', 'max_amount', 'take_amount', 'remain_amount', 'unclaimed_amount', 'receive_amount']
        response = StreamingHttpResponse(self.get_cashactivitylevel_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('微信礼包机构分配明细.csv'))
        return response

    def get_cashactivitylevel_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                # if col == 'unclaimed_number':
                #     tmp_str += '"%s",' % (row['take_number']-row['receive_number'])
                # elif col == 'wait_number':
                #     tmp_str += '"%s",' % (row['receive_number']-row['use_number'])
                if col in ['p_max_amount', 'p_take_amount', 'p_remain_amount', 'p_unclaimed_amount', 'p_receive_amount', 'max_amount', 'take_amount', 'remain_amount', 'unclaimed_amount', 'receive_amount']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else 0)
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def cash_distributed_statistical(self, request):
        cash_activity_id = self.request.query_params.get('cash_activity_id', None)
        if not cash_activity_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        instance = self.get_queryset().filter(cash_activity_id=cash_activity_id).order_by('level__level').first()
        if not instance:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        serializer = CashActivityLevelStatisticalModelSerializer(instance)
        return Response({"statisticsData": serializer.data, 'date_now': datetime.datetime.now().strftime('%Y年%m月%d日')}, status=status.HTTP_200_OK)

    def cashactivitylevel_tree(self, requset):
        cash_activity_id = self.request.query_params.get('cash_activity_id', None)
        if not cash_activity_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        instance = self.get_queryset().filter(cash_activity_id=cash_activity_id).order_by('level__level').first()
        if not instance:
            return Response({}, status=status.HTTP_200_OK)
        serializer = CashActivityLevelTreeModelSerializer(instance)
        return Response(serializer.data, status=status.HTTP_200_OK)

    def cashactivitylevel_tree1(self, requset):
        cash_activity_id = self.request.query_params.get('cash_activity_id', None)
        cash_activity_level_id = self.request.query_params.get('cash_activity_level_id', None)
        if not cash_activity_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        if not cash_activity_level_id:
            queryset = self.get_queryset().filter(cash_activity_id=cash_activity_id).order_by('level__level')[:1]
        else:
            try:
                instance = self.get_queryset().get(id=cash_activity_level_id)
            except:
                return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
            level = instance.level
            if level.level == 1:
                queryset = self.get_queryset().filter(child_set__parent_id=instance.id, level__level=2)
            elif level.level == 2:
                queryset = self.get_queryset().filter(child_set__parent_id=instance.id, level__level=3)
            elif level.level == 3:
                queryset = self.get_queryset().filter(child_set__parent_id=instance.id, level__level=4)
            elif level.level == 0:
                queryset = self.get_queryset().filter(child_set__parent_id=instance.id, level__level=1)
            else:
                queryset = self.get_queryset().filter(level__level=5)
        # if not instance:
        #     return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        serializer = CashActivityLevelListModelSerializer(queryset, many=True)
        return Response(serializer.data, status=status.HTTP_200_OK)


class CashActivityDataModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = CashActivityDataFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('customer_user__mobile', 'mobile')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        cash_activity_level_id = self.request.query_params.get('cash_activity_level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        try:
            if '_mutable' in self.request.data.__dict__:
                self.request.data.__dict__['_mutable'] = True
        except:
            pass
        if level.level == 0:
            queryset = CashActivityData.objects.all().order_by('-id')
        elif level.level == 1:
            queryset = CashActivityData.objects.filter(level__prov_id=level.id).order_by('-id')
        elif level.level == 2:
            queryset = CashActivityData.objects.filter(level__city_id=level.id).order_by('-id')
        elif level.level == 3:
            queryset = CashActivityData.objects.filter(level__district_id=level.id).order_by('-id')
        else:
            queryset = CashActivityData.objects.filter(level__branch_id=level.id).order_by('-id')
        if cash_activity_level_id:
            try:
                activity_level = CashActivityLevel.objects.get(id=cash_activity_level_id)
                self.request.data['acitivty_info'] = CashActivityLevelListModelSerializer(activity_level).data
                relation_set = list(activity_level.parent_set.all().values_list('child_id', flat=True))
                relation_set.append(cash_activity_level_id)
                queryset = queryset.filter(cash_activity_level_id__in=relation_set)
            except:
                self.request.data['acitivty_info'] = {}
            # try:
            #     cash_activity_level = CashActivityLevel.objects.get(id=cash_activity_level_id)
            #     queryset = queryset.filter(cash_activity_id=cash_activity_level.cash_activity_id)
            # except:
            #     queryset = queryset.filter(cash_activity_level_id=cash_activity_level_id)
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return CashActivityDataListModelSerializer
        else:
            return CashActivityDataRetrieveModelSerializer

    def export_cashactivitydata(self, request):
        user = self.request.iser
        activity_id = self.request.query_params.get('activity_id', None)
        cash_activity_level_id = self.request.query_params.get('cash_activity_level_id', None)
        level_id = self.request.query_params.get('level_id', None)
        state = self.request.query_params.get('state', None)
        search = self.request.query_params.get('search', None)
        create_time_begin = self.request.query_params.get('create_time_begin', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND mobile LIKE "%{search}%"'
        if activity_id:
            condition = condition + f' AND cash_activity_id in ({activity_id})'
        if state:
            condition = condition + f' AND state in ({state})'
        if create_time_begin:
            condition = condition + f' AND create_time >= "{create_time_begin + " 00:00:00"}"'
        if create_time_end:
            condition = condition + f' AND create_time <= "{create_time_end + " 23:59:59"}"'
        if cash_activity_level_id:
            activity_level = CashActivityLevel.objects.get(id=cash_activity_level_id)
            relation_set = list(activity_level.parent_set.all().values_list('child_id', flat=True))
            relation_set.append(cash_activity_level_id)
            if len(relation_set) == 1:
                condition = condition + f' AND cash_activity_level_id IN {str(tuple(relation_set)).replace(",", "")}'
            else:
                condition = condition + f' AND cash_activity_level_id IN {tuple(relation_set)}'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT i.*,j.activity_name FROM (SELECT g.*, h.account,branch_name,city_name,district_name FROM (SELECT e.*, f.companyuser_account,companyuser_banch FROM (SELECT c.*,d.idcode,realname,openid FROM (SELECT a.*,b.companyuser_level_id,companyuser_mobile,companyuser_name FROM (SELECT id,cash_activity_id,amount,state,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,DATE_FORMAT(expired_time,'%Y-%m-%d %H:%i:%S')AS expired_time,customer_user_id,companyuser_id,payment_no,return_msg,level_id,mchid,mobile FROM tb_cash_activity_data {condition})AS a LEFT JOIN (SELECT id,nickname AS companyuser_name,mobile AS companyuser_mobile,level_id AS companyuser_level_id FROM tb_u_user)AS b ON a.companyuser_id=b.id)AS c LEFT JOIN (SELECT id,realname,idcode,new_openid AS openid FROM tb_u_customer_user)AS d ON c.customer_user_id=d.id)AS e LEFT JOIN (SELECT id,name AS companyuser_banch,account AS companyuser_account FROM tb_u_level)AS f ON e.companyuser_level_id=f.id)AS g LEFT JOIN (SELECT id,name AS branch_name,account,city_name,district_name FROM tb_u_level)AS h ON g.level_id=h.id)AS i LEFT JOIN (SELECT id,name AS activity_name FROM tb_cash_activity)AS j ON i.cash_activity_id=j.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT i.*,j.activity_name FROM (SELECT g.*, h.account,branch_name,city_name,district_name FROM (SELECT e.*, f.companyuser_account,companyuser_banch FROM (SELECT c.*,d.idcode,realname,openid FROM (SELECT a.*,b.companyuser_level_id,companyuser_mobile,companyuser_name FROM (SELECT id,cash_activity_id,amount,state,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,DATE_FORMAT(expired_time,'%Y-%m-%d %H:%i:%S')AS expired_time,customer_user_id,companyuser_id,payment_no,return_msg,level_id,mchid,mobile FROM tb_cash_activity_data)AS a LEFT JOIN (SELECT id,nickname AS companyuser_name,mobile AS companyuser_mobile,level_id AS companyuser_level_id FROM tb_u_user)AS b ON a.companyuser_id=b.id)AS c LEFT JOIN (SELECT id,realname,idcode,new_openid AS openid FROM tb_u_customer_user)AS d ON c.customer_user_id=d.id)AS e LEFT JOIN (SELECT id,name AS companyuser_banch,account AS companyuser_account FROM tb_u_level)AS f ON e.companyuser_level_id=f.id)AS g LEFT JOIN (SELECT id,name AS branch_name,account,city_name,district_name FROM tb_u_level)AS h ON g.level_id=h.id)AS i LEFT JOIN (SELECT id,name AS activity_name FROM tb_cash_activity)AS j ON i.cash_activity_id=j.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '活动名称', '活动id', '所属市公司', '所属区县', '所属网点', '领取金额', '领取状态', '派发时间', '领取时间', '撤销时间', '用户openid', '领取人姓名', '领取人手机号',
                  '领取人身份证', '派发员工', '派发员工手机号', '派发员工所属网点', '派发员工网点机构号', '领取渠道', '派发服务商号', '派发流水号', '失败原因']
        cols = ['id', 'activity_name', 'cash_activity_id', 'city_name', 'district_name', 'branch_name', 'amount', 'state', 'create_time', 'received_time', 'expired_time', 'openid', 'realname', 'mobile', 'idcode',
                'companyuser_name', 'companyuser_mobile', 'companyuser_banch', 'companyuser_account', 'mchid_channel', 'mchid', 'payment_no', 'return_msg']
        response = StreamingHttpResponse(self.get_cashactivitydata_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('优惠券派发核销数据.csv'))
        return response

    def get_cashactivitydata_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        STATE_ENUM = {
            1: '待领取',
            2: '已领取',
            3: '领取失败',
            4: '已撤销'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'state':
                    tmp_str += '"%s",' % (STATE_ENUM[row[col]])
                elif col in ['idcode', 'payment_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col == 'expired_time':
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row['state'] == 4 else '')
                elif col == 'mchid_channel':
                    if row['state'] in [2, 3]:
                        if row['mchid'] == 'alipay':
                            tmp_str += '"%s",' % '支付宝'
                        else:
                            tmp_str += '"%s",' % '微信'
                    else:
                        tmp_str += '"%s",' % ''
                elif col == 'mchid':
                    if row['state'] in [2, 3]:
                        if row[col] == 'alipay':
                            tmp_str += '"%s",' % "'2088631976752358"
                        else:
                            tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                    else:
                        tmp_str += '"%s",' % ''
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantBankQRCodeQuestModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantBankQRCodeQuestFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('user__nickname', )
    serializer_class = MerchantBankQRCodeQuestModelSerializer

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantBankQRCodeQuest.objects.all()
        elif level.level == 1:
            queryset = MerchantBankQRCodeQuest.objects.filter(create_user__level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantBankQRCodeQuest.objects.filter(create_user__level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantBankQRCodeQuest.objects.filter(create_user__level__district_id=level.id)
        else:
            queryset = MerchantBankQRCodeQuest.objects.filter(create_user__level__branch_id=level.id)
        return queryset.order_by('-id')

    def create(self, request, *args, **kwargs):
        my_file = self.request.FILES.get('my_file', None)
        file_type = self.request.data.get('file_type', None)
        user = self.request.iser
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        if str(file_type) not in ['1', '2', '3']:
            return Response({'error': '上传类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        nrows = table.nrows
        title = table.row_values(0)
        if str(file_type) == '1':
            if '员工编码' not in title:
                return Response({'error': '表格字段缺少"员工编码"'}, status=status.HTTP_400_BAD_REQUEST)
            if '子码编码' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
            if '员工码下载地址' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
        elif str(file_type) == '3':
            if '日期' not in title:
                return Response({'error': '表格字段缺少"日期"'}, status=status.HTTP_400_BAD_REQUEST)
            if '当日新增银行首绑量' not in title:
                return Response({'error': '表格字段缺少"当日新增银行首绑量"'}, status=status.HTTP_400_BAD_REQUEST)
            if '当日引导绑卡数' not in title:
                return Response({'error': '表格字段缺少"当日引导绑卡数"'}, status=status.HTTP_400_BAD_REQUEST)
            if '子码编码' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
        else:
            if '绑卡时间' not in title:
                return Response({'error': '表格字段缺少"绑卡时间"'}, status=status.HTTP_400_BAD_REQUEST)
            if '绑卡银行卡号' not in title:
                return Response({'error': '表格字段缺少"绑卡银行卡号"'}, status=status.HTTP_400_BAD_REQUEST)
            if '是否银行首绑' not in title:
                return Response({'error': '表格字段缺少"是否银行首绑"'}, status=status.HTTP_400_BAD_REQUEST)
            if '子码编码' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
            if '绑卡类型' not in title:
                return Response({'error': '表格字段缺少"绑卡类型"'}, status=status.HTTP_400_BAD_REQUEST)
        file_name = f'{my_file.name.replace(type_excel, "").replace(".", "")}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.{type_excel}'
        file = pic_upload(file_contents, file_name)
        check_quest = MerchantBankQRCodeQuest.objects.filter(file_status=2)
        if check_quest:
            file_status = 1
        else:
            file_status = 2
        quest = MerchantBankQRCodeQuest.objects.create(create_user_id=user.id, file=file, file_type=file_type, file_status=file_status, total_line=nrows-1)
        if file_status == 2:
            operation_merchant_bank_quest.delay({'id': quest.id})
            # operation_merchant_bank_quest({'id': quest.id})
        return Response({'success': '上传文件成功'}, status=status.HTTP_200_OK)


class MerchantBankQRCodeModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantBankQRCodeFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('code_id', 'business_no', 'merchant_name')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantBankQRCode.objects.all()
        elif level.level == 1:
            queryset = MerchantBankQRCode.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantBankQRCode.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantBankQRCode.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantBankQRCode.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantBankQRCodeListModelSerializer
        else:
            return MerchantBankQRCodeRetrieveModelSerializer

    def export_bankqrcode(self, request):
        user = self.request.iser
        level_id = self.request.query_params.get('level_id', None)
        search = self.request.query_params.get('search', None)
        is_download = self.request.query_params.get('is_download', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if is_download:
            condition = condition + f' AND is_download in ({is_download})'
        if search:
            condition = condition + f' AND (code_id LIKE "%{search}%" OR business_no LIKE "%{search}%" OR merchant_name LIKE "%{search}%")'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        # try:
        #     sql = f'''
        #     SELECT i.*,j.yesterday_first_number,yesterday_number FROM (SELECT g.*,h.last_month_number,last_month_first_number FROM (SELECT e.*,f.this_month_number,this_month_first_number FROM (SELECT c.*,d.total_first_number,total_number FROM (SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,level_id,is_download,visit FROM tb_merchant_bank_qrcode {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id)AS c LEFT JOIN (SELECT bank_qrcode_id,COUNT(id)AS total_number,SUM(is_first)AS total_first_number FROM tb_merchant_bank_qrcode_bind_data GROUP BY bank_qrcode_id)AS d ON c.id=d.bank_qrcode_id)AS e LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS this_month_number,SUM(is_first)AS this_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE DATE(bind_time) >= CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY AND DATE(bind_time) < DATE_ADD(CURDATE(), INTERVAL 1 MONTH) GROUP BY bank_qrcode_id)AS f ON e.id=f.bank_qrcode_id)AS g LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS last_month_number,SUM(is_first)AS last_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE YEAR(bind_time) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND MONTH(bind_time) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND DAY(bind_time) >= 1 GROUP BY bank_qrcode_id)AS h ON g.id=h.bank_qrcode_id)AS i LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS yesterday_number,SUM(is_first)AS yesterday_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE bind_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND bind_time < CURDATE() GROUP BY bank_qrcode_id)AS j ON i.id=j.bank_qrcode_id;
        #     '''
        #     cursor.execute(sql)
        # except:
        #     sql = f'''
        #     SELECT i.*,j.yesterday_first_number,yesterday_number FROM (SELECT g.*,h.last_month_number,last_month_first_number FROM (SELECT e.*,f.this_month_number,this_month_first_number FROM (SELECT c.*,d.total_first_number,total_number FROM (SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,level_id,is_download,visit FROM tb_merchant_bank_qrcode)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id)AS c LEFT JOIN (SELECT bank_qrcode_id,COUNT(id)AS total_number,SUM(is_first)AS total_first_number FROM tb_merchant_bank_qrcode_bind_data GROUP BY bank_qrcode_id)AS d ON c.id=d.bank_qrcode_id)AS e LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS this_month_number,SUM(is_first)AS this_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE DATE(bind_time) >= CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY AND DATE(bind_time) < DATE_ADD(CURDATE(), INTERVAL 1 MONTH) GROUP BY bank_qrcode_id)AS f ON e.id=f.bank_qrcode_id)AS g LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS last_month_number,SUM(is_first)AS last_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE YEAR(bind_time) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND MONTH(bind_time) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND DAY(bind_time) >= 1 GROUP BY bank_qrcode_id)AS h ON g.id=h.bank_qrcode_id)AS i LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS yesterday_number,SUM(is_first)AS yesterday_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE bind_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND bind_time < CURDATE() GROUP BY bank_qrcode_id)AS j ON i.id=j.bank_qrcode_id;
        #     '''
        #     cursor.execute(sql)
        try:
            sql = f'''SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT * FROM tb_merchant_bank_qrcode {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;'''
            cursor.execute(sql)
        except:
            sql = f'''SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT * FROM tb_merchant_bank_qrcode)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;'''
            cursor.execute(sql)
        ziduan = ['ID', '子码编码', '微邮付商户号', '商户名称', '所属市公司', '所属区县', '所属网点', '机构号', '是否下载', '浏览次数', '累计绑卡数', '累计首绑数']
        cols = ['id', 'code_id', 'business_no', 'merchant_name', 'city_name', 'district_name', 'branch_name', 'account_nine', 'is_download', 'visit', 'total_number', 'total_first_number']
        response = StreamingHttpResponse(self.get_bankqrcode_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商户银行卡绑卡统计.csv'))
        return response

    def get_bankqrcode_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_DOWNLOAD_ENUM = {
            0: '否',
            1: '是'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col in ['yesterday_number', 'yesterday_first_number', 'this_month_number', 'this_month_first_number', 'last_month_number', 'last_month_first_number', 'total_number', 'total_first_number']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '0')
                elif col == 'is_download':
                    tmp_str += '"%s",' % (IS_DOWNLOAD_ENUM[row[col]])
                elif col == 'code_id':
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantBankQRCodeBindDataModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantBankQRCodeBindDataFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('code_id', 'business_no', 'merchant_name')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantBankQRCodeBindData.objects.all()
        elif level.level == 1:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        return MerchantBankQRCodeBindDataListModelSerializer

    def export_bankqrcodedata(self, request):
        user = self.request.iser
        level_id = self.request.query_params.get('level_id', None)
        card_type = self.request.query_params.get('card_type', None)
        is_first = self.request.query_params.get('is_first', None)
        bank_qrcode_id = self.request.query_params.get('bank_qrcode_id', None)
        bind_time_begin = self.request.query_params.get('bind_time_begin', None)
        bind_time_end = self.request.query_params.get('bind_time_end', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (code_id LIKE "%{search}%" OR business_no LIKE "%{search}%" OR merchant_name LIKE "%{search}%")'
        if card_type:
            condition = condition + f' AND card_type in ({card_type})'
        if is_first:
            condition = condition + f' AND is_first in ({is_first})'
        if bank_qrcode_id:
            condition = condition + f' AND bank_qrcode_id in ({bank_qrcode_id})'
        if bind_time_begin:
            condition = condition + f' AND bind_time >= "{bind_time_begin + " 00:00:00"}"'
        if bind_time_end:
            condition = condition + f' AND bind_time <= "{bind_time_end + " 23:59:59"}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,is_first,card_type,card_number,level_id FROM tb_merchant_bank_qrcode_bind_data {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,is_first,card_type,card_number,level_id FROM tb_merchant_bank_qrcode_bind_data)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '子码编码', '微邮付商户号', '商户名称', '所属市公司', '所属区县', '所属网点', '机构号', '绑卡类型', '绑卡时间', '绑卡银行卡号', '是否银行首绑']
        cols = ['id', 'code_id', 'business_no', 'merchant_name', 'city_name', 'district_name', 'branch_name', 'account_nine', 'bind_time', 'card_type', 'bind_time', 'card_number', 'is_first']
        response = StreamingHttpResponse(self.get_bankqrcodedata_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商户银行卡绑卡明细.csv'))
        return response

    def get_bankqrcodedata_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_FIRST_ENUM = {
            0: '否',
            1: '是'
        }
        CARD_TYPE_ENUM = {
            1: '储蓄卡',
            2: '信用卡'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_first':
                    tmp_str += '"%s",' % (IS_FIRST_ENUM[row[col]])
                elif col == 'card_type':
                    tmp_str += '"%s",' % (CARD_TYPE_ENUM[row[col]])
                elif col in ['idcode', 'payment_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantBankQPCodeStatisticsModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MErchantBankQPCodeStatisticsFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('code_id', 'business_no', 'merchant_name')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MErchantBankQPCodeStatistics.objects.all()
        elif level.level == 1:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__district_id=level.id)
        else:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        return MErchantBankQPCodeStatisticsListModelSerializer

    def export_bankqrcodestatistics(self, request):
        user = self.request.iser
        level_id = self.request.query_params.get('level_id', None)
        bank_qrcode_id = self.request.query_params.get('bank_qrcode_id', None)
        bind_time_begin = self.request.query_params.get('bind_time_begin', None)
        bind_time_end = self.request.query_params.get('bind_time_end', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (code_id LIKE "%{search}%" OR business_no LIKE "%{search}%" OR merchant_name LIKE "%{search}%")'
        if bank_qrcode_id:
            condition = condition + f' AND bank_qrcode_id in ({bank_qrcode_id})'
        if bind_time_begin:
            condition = condition + f' AND bind_time >= "{bind_time_begin + " 00:00:00"}"'
        if bind_time_end:
            condition = condition + f' AND bind_time <= "{bind_time_end + " 23:59:59"}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,level_id,total_number,total_first_number FROM tb_merchant_bank_qrcode_statistics {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,level_id,total_number,total_first_number FROM tb_merchant_bank_qrcode_statistics)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '子码编码', '微邮付商户号', '商户名称', '所属市公司', '所属区县', '所属网点', '机构号', '绑卡时间', '当日引导绑卡数', '当日新增银行首绑量']
        cols = ['id', 'code_id', 'business_no', 'merchant_name', 'city_name', 'district_name', 'branch_name', 'account_nine', 'bind_time', 'total_number', 'total_first_number']
        response = StreamingHttpResponse(self.get_bankqrcodestatistics_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商户银行卡绑卡统计.csv'))
        return response

    def get_bankqrcodestatistics_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_FIRST_ENUM = {
            0: '否',
            1: '是'
        }
        CARD_TYPE_ENUM = {
            1: '储蓄卡',
            2: '信用卡'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_first':
                    tmp_str += '"%s",' % (IS_FIRST_ENUM[row[col]])
                elif col == 'card_type':
                    tmp_str += '"%s",' % (CARD_TYPE_ENUM[row[col]])
                elif col in ['idcode', 'payment_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"